//
//  DataBase.m
//  WorldOfFacts_V2
//
//  Created by Demjanko Denis on 09.11.12.
//  Copyright (c) 2012 it-dimension. All rights reserved.
//

#import "DataBase.h"
#import <sqlite3.h>

@implementation DataBase

static sqlite3 *database = nil;

#pragma mark - init

+(DataBase*)sharedManeger{
    __strong static DataBase* _sharedManager = nil;
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        _sharedManager = [[DataBase alloc]init];
    });
    return _sharedManager;
}

-(id)init{
    self = [super init];
    
    [self copyDatabaseIfNeeded];
    
    appDelegate = (AppDelegate*)[[UIApplication sharedApplication]delegate];
    
    categories = [[NSMutableDictionary alloc] initWithContentsOfFile:
                  [[NSBundle mainBundle]pathForResource:@"categories_names" ofType:@"plist"]];
    
    [self updateIsUpdate];
    
    return self;
}

#pragma mark - files

- (void) copyDatabaseIfNeeded {
	NSFileManager *fileManager = [NSFileManager defaultManager];
	NSError *error;
	NSString *dbPath = [self getDBPath];
	BOOL success = [fileManager fileExistsAtPath:dbPath]; 
	if(!success) {
		NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"facts_db.sqlite"];
		success = [fileManager copyItemAtPath:defaultDBPath toPath:dbPath error:&error];
		if (!success) 
			NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
	}	
}

- (NSString *) getDBPath {
	NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
	NSString *documentsDir = [paths objectAtIndex:0];
	return [documentsDir stringByAppendingPathComponent:@"facts_db.sqlite"];
}

#pragma mark - add

-(void)addFacts:(NSMutableArray*)factsArray{
	NSString *dbPath = [self getDBPath];	
	
    sqlite3_stmt *addStmt = nil;
    
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
		
       
        for (int i=0; i<factsArray.count; i++) {

            @autoreleasepool {
                                
                NSMutableDictionary *fact = [NSMutableDictionary dictionaryWithDictionary:[factsArray objectAtIndex:i]];
                
                if(addStmt == nil) {
                    const char *sql = "insert OR ignore into facts(fact_id,full_description,mini_description,number_of_likes,number_of_comments,images,videos,date,category_id,is_like,is_read,is_favorite,icon) Values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
                    
                    if(sqlite3_prepare_v2(database, sql, -1, &addStmt, NULL) != SQLITE_OK)
                        NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));
                }
                
                sqlite3_bind_int (addStmt, 1, [[fact valueForKey:@"fact_id"] intValue]);
                sqlite3_bind_text(addStmt, 2, [[fact valueForKey:@"full_description"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(addStmt, 3, [[fact valueForKey:@"mini_description"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_int (addStmt, 4, [[fact valueForKey:@"number_of_likes"] intValue]);
                sqlite3_bind_int (addStmt, 5, [[fact valueForKey:@"number_of_comments"] intValue]);
                sqlite3_bind_text(addStmt, 6, [[fact valueForKey:@"images"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(addStmt, 7, [[fact valueForKey:@"videos"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_text(addStmt, 8, [[fact valueForKey:@"date"] UTF8String], -1, SQLITE_TRANSIENT);
                sqlite3_bind_int (addStmt, 9, [[fact valueForKey:@"category_id"] intValue]);
                sqlite3_bind_int (addStmt, 10, [[fact valueForKey:@"is_like"] intValue]);
                sqlite3_bind_int (addStmt, 11, [[fact valueForKey:@"is_read"] intValue]);
                sqlite3_bind_int (addStmt, 12, [[fact valueForKey:@"is_favorite"] intValue]);
                sqlite3_bind_text(addStmt, 13, [[fact valueForKey:@"icon"] UTF8String], -1, SQLITE_TRANSIENT);

                
                if(SQLITE_DONE != sqlite3_step(addStmt))
                    NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
                
                sqlite3_finalize(addStmt);
                addStmt = nil; 
                
            }

        }
         
	}

	sqlite3_close(database);
}

#pragma mark - select

-(NSMutableDictionary*)getFact:(int)fact_id{
    NSMutableDictionary *fact = [NSMutableDictionary dictionary];;
	
	NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string  = [NSString stringWithFormat:@"select fact_id,full_description,number_of_likes,number_of_comments,images,videos,is_like,is_favorite,category_id,is_update from facts where fact_id = %d",fact_id];
        
        sql = [sql_string UTF8String];
        
		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                
                NSString* escapedUrlString = [[[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 4)] stringByReplacingOccurrencesOfString:@"\"" withString:@""] stringByAddingPercentEscapesUsingEncoding:NSUTF8StringEncoding];

                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 0)] forKey:@"fact_id"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)] forKey:@"full_description"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 2)] forKey:@"number_of_likes"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 3)] forKey:@"number_of_comments"];
                [fact setObject:[NSMutableArray arrayWithArray:[escapedUrlString componentsSeparatedByString:@";"]] forKey:@"images"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 5)] forKey:@"video"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 6)] forKey:@"is_like"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 7)] forKey:@"is_favorite"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 8)] forKey:@"category_id"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 9)] forKey:@"update"];
			}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);
    
	return fact; 
}

-(NSMutableArray*)getFactsIds:(int)category_id fromFact_id:(int)fromFact_id factType:(int)factsType{
    NSMutableArray *ids = [NSMutableArray array];;
	
	NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string = nil;
        
        if(category_id==0)
            sql_string = [NSString stringWithFormat:@"select fact_id,is_read from facts order by fact_id DESC"];
        else
            sql_string = [NSString stringWithFormat:@"select fact_id,is_read from facts where category_id = %d order by fact_id DESC",category_id];

        sql = [sql_string UTF8String];
        
		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
				
                int isRead = sqlite3_column_int(selectstmt, 1);
                
                BOOL isAdd = FALSE;
                
                if(factsType==1 && isRead==1)
                    isAdd = TRUE;
                else if(factsType==2 && isRead==0)
                    isAdd = TRUE;
                else if(factsType==0)
                    isAdd = TRUE;
                
                if(isAdd){
                    [ids addObject:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 0)]]; 
                }
                
			}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);
    
	return ids; 
}

-(NSMutableArray*)getFactsIdsFavorites{
    NSMutableArray *ids = [NSMutableArray array];;
	
	NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string = [NSString stringWithFormat:@"select fact_id from facts where is_favorite = 1 order by add_time DESC"];

        sql = [sql_string UTF8String];
        
		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                [ids addObject:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 0)]]; 
			}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);
    
	return ids; 
}

-(BOOL)isFactAvailabe:(int)fact_id{
    BOOL available = FALSE;
    
    NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string = [NSString stringWithFormat:@"select fact_id from facts where fact_id = %d",fact_id];
        
        sql = [sql_string UTF8String];
        
		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                available = TRUE;
			}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);
    
    return available;
}

-(NSMutableArray*)getFacts:(int)category_id fromFact_id:(int)fromFact_id factType:(int)factsType{
    
    NSMutableArray *factsArray = [NSMutableArray array];;
	
	NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string = nil;
        
        if(fromFact_id==0){
            if(category_id==0)
                sql_string = @"select fact_id,mini_description,number_of_likes,date,is_read,is_favorite,icon from facts order by fact_id DESC LIMIT 100";
            else
                sql_string = [NSString stringWithFormat:@"select fact_id,mini_description,number_of_likes,date,is_read,is_favorite,icon from facts where category_id = %d order by fact_id DESC LIMIT 100",category_id];
        }
        else{
            if(category_id==0)
                sql_string = [NSString stringWithFormat:@"select fact_id,mini_description,number_of_likes,date,is_read,is_favorite,icon from facts where fact_id <%d order by fact_id DESC LIMIT 100",fromFact_id];
            else
                sql_string = [NSString stringWithFormat:@"select fact_id,mini_description,number_of_likes,date,is_read,is_favorite,icon from facts where category_id = %d and fact_id <%d order by fact_id DESC LIMIT 100",category_id,fromFact_id];
        }
        

        if(factsType==1 || factsType==2){
            NSRange textRange =[sql_string rangeOfString:@"where"];
            NSString *str_1;
            NSString *str_2;

            if(textRange.location != NSNotFound)
            {
                NSRange orderRange =[sql_string rangeOfString:@"order"];

                int read = 1;
                if(factsType==2)
                    read = 0;
                
                str_1 = [sql_string substringToIndex:orderRange.location];
                str_2 = [NSString stringWithFormat:@" and is_read=%d order by fact_id DESC LIMIT 100",read];
            }
            else{
                NSRange orderRange =[sql_string rangeOfString:@"order"];

                int read = 1;
                if(factsType==2)
                    read = 0;
                
                str_1 = [sql_string substringToIndex:orderRange.location];
                str_2 = [NSString stringWithFormat:@" where is_read=%d order by fact_id DESC LIMIT 100",read];
            }
            
            sql_string = [NSString stringWithFormat:@"%@%@",str_1,str_2];
        }
                
        sql = [sql_string UTF8String];
        
		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                NSMutableDictionary *fact = [NSMutableDictionary dictionary];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 0)] forKey:@"fact_id"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)] forKey:@"mini_description"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 2)] forKey:@"number_of_likes"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 3)] forKey:@"date"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 4)] forKey:@"is_read"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 5)] forKey:@"is_favorite"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 6)] forKey:@"icon"];
                [factsArray addObject:fact]; 
	}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);
    
	return factsArray;    
}

-(NSMutableDictionary*)getMiniFact:(int)fact_id{
    NSMutableDictionary *fact = [NSMutableDictionary dictionary];;
	
	NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string  = [NSString stringWithFormat:@"select fact_id,mini_description,number_of_likes,date,is_read,is_favorite,icon from facts where fact_id = %d",fact_id];
        
        sql = [sql_string UTF8String];
        
		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 0)] forKey:@"fact_id"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)] forKey:@"mini_description"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 2)] forKey:@"number_of_likes"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 3)] forKey:@"date"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 4)] forKey:@"is_read"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 5)] forKey:@"is_favorite"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 6)] forKey:@"icon"];
			}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);
    
	return fact;  
}

-(BOOL)isMoreFactsAvailable:(int)category_id fromFact_id:(int)fromFact_id factType:(int)factsType{
    BOOL available = FALSE;
    
    NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string = nil;

        if(category_id==0){
            sql_string = [NSString stringWithFormat:@"select fact_id from facts where fact_id <%d order by fact_id DESC LIMIT 1",fromFact_id];

            if(factsType==1){
                sql_string = [NSString stringWithFormat:@"select fact_id from facts where fact_id <%d and is_read = 1 order by fact_id DESC LIMIT 1",fromFact_id];
            }
            else if(factsType==2){
                sql_string = [NSString stringWithFormat:@"select fact_id from facts where fact_id <%d and is_read = 0 order by fact_id DESC LIMIT 1",fromFact_id]; 
            }
                
        }
        else{
           sql_string = [NSString stringWithFormat:@"select fact_id from facts where category_id = %d and fact_id <%d order by fact_id DESC LIMIT 1",category_id,fromFact_id];  
            
            if(factsType==1){
                   sql_string = [NSString stringWithFormat:@"select fact_id from facts where category_id = %d and fact_id <%d and is_read = 1 order by fact_id DESC LIMIT 1",category_id,fromFact_id];  
            }
            else if(factsType==2){
                   sql_string = [NSString stringWithFormat:@"select fact_id from facts where category_id = %d and fact_id <%d and is_read = 0 order by fact_id DESC LIMIT 1",category_id,fromFact_id];  
            }
        }
             
        
        sql = [sql_string UTF8String];

		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                available = TRUE;                
			}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);

    return available;
}

-(int)isNextFactAvailable:(int)category_id fromFact_id:(int)fromFact_id factType:(int)factsType{
    int available = 0;
    
    NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string = nil;
        
        if(category_id==0){
            sql_string = [NSString stringWithFormat:@"select fact_id from facts where fact_id <%d order by fact_id DESC LIMIT 1",fromFact_id];
            
            if(factsType==1){
                sql_string = [NSString stringWithFormat:@"select fact_id from facts where fact_id <%d and is_read = 1 order by fact_id DESC LIMIT 1",fromFact_id];
            }
            else if(factsType==2){
                sql_string = [NSString stringWithFormat:@"select fact_id from facts where fact_id <%d and is_read = 0 order by fact_id DESC LIMIT 1",fromFact_id]; 
            }
            
        }
        else{
            sql_string = [NSString stringWithFormat:@"select fact_id from facts where category_id = %d and fact_id <%d order by fact_id DESC LIMIT 1",category_id,fromFact_id];  
            
            if(factsType==1){
                sql_string = [NSString stringWithFormat:@"select fact_id from facts where category_id = %d and fact_id <%d and is_read = 1 order by fact_id DESC LIMIT 1",category_id,fromFact_id];  
            }
            else if(factsType==2){
                sql_string = [NSString stringWithFormat:@"select fact_id from facts where category_id = %d and fact_id <%d and is_read = 0 order by fact_id DESC LIMIT 1",category_id,fromFact_id];  
            }
        }
        
        
        sql = [sql_string UTF8String];
        
		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                available = sqlite3_column_int(selectstmt, 0);                
			}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);
    
    return available;
}

-(int)isPrevFactAvailable:(int)category_id fromFact_id:(int)fromFact_id factType:(int)factsType{
    int available = 0;
    
    NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string = nil;
        
        if(category_id==0){
            sql_string = [NSString stringWithFormat:@"select fact_id from facts where fact_id >%d order by fact_id ASC LIMIT 1",fromFact_id];
            
            if(factsType==1){
                sql_string = [NSString stringWithFormat:@"select fact_id from facts where fact_id >%d and is_read = 1 order by fact_id ASC LIMIT 1",fromFact_id];
            }
            else if(factsType==2){
                sql_string = [NSString stringWithFormat:@"select fact_id from facts where fact_id >%d and is_read = 0 order by fact_id ASC LIMIT 1",fromFact_id]; 
            }
            
        }
        else{
            sql_string = [NSString stringWithFormat:@"select fact_id from facts where category_id = %d and fact_id >%d order by fact_id ASC LIMIT 1",category_id,fromFact_id];  
            
            if(factsType==1){
                sql_string = [NSString stringWithFormat:@"select fact_id from facts where category_id = %d and fact_id >%d and is_read = 1 order by fact_id ASC LIMIT 1",category_id,fromFact_id];  
            }
            else if(factsType==2){
                sql_string = [NSString stringWithFormat:@"select fact_id from facts where category_id = %d and fact_id >%d and is_read = 0 order by fact_id ASC LIMIT 1",category_id,fromFact_id];  
            }
        }
        
        
        sql = [sql_string UTF8String];
        
		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                available = sqlite3_column_int(selectstmt, 0);                
			}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);
    
    return available;
}

#pragma mark - get

-(NSString*)getCategoryName:(NSString*)category_id{    
    return [NSString stringWithFormat:@"Категория: %@",[categories objectForKey:category_id]];
}


#pragma mark - favorites & read

-(int)getLastFactId{
    int fact_id = 0;
	
	NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string  = @"select fact_id from facts ORDER BY fact_id DESC LIMIT 1";
        
        sql = [sql_string UTF8String];
        
		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
                fact_id = sqlite3_column_int(selectstmt, 0);
			}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);
    
	return fact_id;
}

-(NSMutableArray*)getFavorites{
    NSMutableArray *factsArray = [NSMutableArray array];;
	
	NSString *dbPath = [self getDBPath];	
	
	if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        const char *sql = nil;
        
        NSString *sql_string  = @"select fact_id,mini_description,number_of_likes,date,icon from facts where is_favorite = 1 ORDER BY add_time DESC";
        
        sql = [sql_string UTF8String];
        
		sqlite3_stmt *selectstmt = nil;
		
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) == SQLITE_OK) {
			
			while(sqlite3_step(selectstmt) == SQLITE_ROW) {
				
                NSMutableDictionary *fact = [NSMutableDictionary dictionary];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 0)] forKey:@"fact_id"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 1)] forKey:@"mini_description"];
                [fact setValue:[NSString stringWithFormat:@"%d",sqlite3_column_int(selectstmt, 2)] forKey:@"number_of_likes"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 3)] forKey:@"date"];
                [fact setValue:[NSString stringWithUTF8String:(char *)sqlite3_column_text(selectstmt, 4)] forKey:@"icon"];
                
                [factsArray addObject:fact];                 
			}
            
            sqlite3_finalize(selectstmt);
            selectstmt = nil;
		}
	}
	
    sqlite3_close(database);
    
	return factsArray; 
}


-(void)updateFavorites:(int)fact_id is_Favorite:(int)is_Favorite{
    NSString *dbPath = [self getDBPath];	

    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        sqlite3_stmt *selectstmt = nil;

		const char *sql = "update facts set is_favorite=?, add_time=? where fact_id = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) != SQLITE_OK)
			NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(database));
				
		sqlite3_bind_int(selectstmt, 1, is_Favorite);
		sqlite3_bind_int(selectstmt, 2, [[NSDate date] timeIntervalSince1970]);
		sqlite3_bind_int(selectstmt, 3, fact_id);
		
		if(SQLITE_DONE != sqlite3_step(selectstmt))
			NSAssert1(0, @"Error while updating data. '%s'", sqlite3_errmsg(database));
		
        sqlite3_finalize(selectstmt);
        selectstmt = nil;
	}

    sqlite3_close(database);
}

-(void)updateFactInfo:(int)fact_id likes:(int)likes comments:(int)comments isLike:(int)isLike{
    NSString *dbPath = [self getDBPath];	
    
    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        sqlite3_stmt *selectstmt = nil;
        
		const char *sql = "update facts set number_of_likes=?, number_of_comments=?,is_like=?,is_update=1 where fact_id = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) != SQLITE_OK)
			NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(database));
        
		sqlite3_bind_int(selectstmt, 1, likes);
		sqlite3_bind_int(selectstmt, 2, comments);
		sqlite3_bind_int(selectstmt, 3, isLike);
        sqlite3_bind_int(selectstmt, 4, fact_id);

		if(SQLITE_DONE != sqlite3_step(selectstmt))
			NSAssert1(0, @"Error while updating data. '%s'", sqlite3_errmsg(database));
		
        sqlite3_finalize(selectstmt);
        selectstmt = nil;
	}
    
    sqlite3_close(database);   
}

-(void)updateLikes:(int)fact_id likes:(int)likes is_like:(int)is_like{
    NSString *dbPath = [self getDBPath];	
    
    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        sqlite3_stmt *selectstmt = nil;
        
		const char *sql = "update facts set number_of_likes=?,is_like=? where fact_id = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) != SQLITE_OK)
			NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(database));
        
		sqlite3_bind_int(selectstmt, 1, likes);
		sqlite3_bind_int(selectstmt, 2, is_like);
        sqlite3_bind_int(selectstmt, 3, fact_id);

		if(SQLITE_DONE != sqlite3_step(selectstmt))
			NSAssert1(0, @"Error while updating data. '%s'", sqlite3_errmsg(database));
		
        sqlite3_finalize(selectstmt);
        selectstmt = nil;
	}
    
    sqlite3_close(database);
}

-(void)updateIsUpdate{
    NSString *dbPath = [self getDBPath];	
    
    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        sqlite3_stmt *selectstmt = nil;
        
		const char *sql = "update facts set is_update=0";
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) != SQLITE_OK)
			NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(database));
                
		if(SQLITE_DONE != sqlite3_step(selectstmt))
			NSAssert1(0, @"Error while updating data. '%s'", sqlite3_errmsg(database));
		
        sqlite3_finalize(selectstmt);
        selectstmt = nil;
	}
    
    sqlite3_close(database);
}

-(void)setReadToFact:(int)fact_id{
    NSString *dbPath = [self getDBPath];	
    
    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {
        
        sqlite3_stmt *selectstmt = nil;
        
		const char *sql = "update facts set is_read=? where fact_id = ?";
		if(sqlite3_prepare_v2(database, sql, -1, &selectstmt, NULL) != SQLITE_OK)
			NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(database));
        
		sqlite3_bind_int(selectstmt, 1, 1);
		sqlite3_bind_int(selectstmt, 2, fact_id);
		
		if(SQLITE_DONE != sqlite3_step(selectstmt))
			NSAssert1(0, @"Error while updating data. '%s'", sqlite3_errmsg(database));
		
        sqlite3_finalize(selectstmt);
        selectstmt = nil;
	}
    
    sqlite3_close(database); 
}

@end
